#!/bin/env python

import time
import ConfigParser,MySQLdb,email.MIMEText,smtplib

def _config(file,section,option):
	cf=ConfigParser.ConfigParser()
	cf.read(file)
	return cf.get(section,option)

def _mysql_query(host,port,user,passwd,db,query):
	db_con=MySQLdb.connect(host=host,user=user,passwd=passwd,db=db,port=int(port),use_unicode=True,charset="UTF8")
	cur=db_con.cursor()
	cur.execute(query)
	result=cur.fetchall()
	db_con.close()
	return result


config_file="/root/config.ini"
db_section="DB"
mail_section="MAIL"
slave_section="SLAVE"
html_file="/usr/local/lighttpd/htdocs/slave_check.html"


##config db options
config_db_host=_config(config_file,db_section,"Host")
config_db_port=_config(config_file,db_section,"Port")
config_db_user=_config(config_file,db_section,"User")
config_db_pass=_config(config_file,db_section,"Pass")
config_db_db=_config(config_file,db_section,"Db")
config_db_table=_config(config_file,db_section,"Table")

##config mail options
config_mail_smtphost=_config(config_file,mail_section,"Smtp_host")
config_mail_from=_config(config_file,mail_section,"From")
config_mail_to=_config(config_file,mail_section,"To")

##config slave options
config_slave_user=_config(config_file,slave_section,"User")
config_slave_pass=_config(config_file,slave_section,"Pass")



##query zone info SQL
zone_query="select id,name,ip,port,db_name,mail_check from "+config_db_table

#query slave status info SQL
slave_status="show slave status"

##query all zone info
zone_info=_mysql_query(config_db_host,config_db_port,config_db_user,config_db_pass,config_db_db,zone_query)

##html header
zone_html="""
	<html>
	<title>:::mysql replaction check:::</title>
	<body>
	<center><h2>mysql replaction check</h2>
	<table border="1" align="center" bordercolorlight="#999999" bordercolordark="#E0E0E0">
	<tr bgcolor="e2ebf1" valign="middle">
	   <td>id</td>
	   <td>zone_name</td>
	   <td>master_ip</td>
	   <td>slave_ip</td>
	   <td>io_running</td>
	   <td>sql_running</td>
	   <td>last_error</td>
	   <td>behind_master(s)</td>
	   <td>query_time</td>
	</tr>
	"""

for eachZone in zone_info:
	(slave_id,slave_name,slave_ip,slave_port,slave_db,slave_mail_check)=eachZone
	current_time=time.strftime("%Y-%m-%d %H:%M:%S")
	mail_message=""
	slave_result=_mysql_query(slave_ip,slave_port,config_slave_user,config_slave_pass,"test",slave_status)
	((Slave_IO_State,Master_Host,Master_User,Master_Port,Connect_Retry,Master_Log_File,Read_Master_Log_Pos,Relay_Log_File,Relay_Log_Pos,Relay_Master_Log_File,Slave_IO_Running,Slave_SQL_Running,Replicate_Do_DB,Replicate_Ignore_DB,Replicate_Do_Table,Replicate_Ignore_Table,Replicate_Wild_Do_Table,Replicate_Wild_Ignore_Table,Last_Errno,Last_Error,Skip_Counter,Exec_Master_Log_Pos,Relay_Log_Space,Until_Condition,Until_Log_File,Until_Log_Pos,Master_SSL_Allowed,Master_SSL_CA_File,Master_SSL_CA_Path,Master_SSL_Cert,Master_SSL_Cipher,Master_SSL_Key,Seconds_Behind_Master),)=slave_result
	if Slave_IO_Running !="Yes":
		mail_message+="%s ----------> Slave_IO_Running:%s\n" %(slave_ip,Slave_IO_Running)
		Slave_IO_Running="<b><font color=\"#FF0000\">"+Slave_IO_Running+"</font></b>"
	if Slave_SQL_Running !="Yes":
		mail_message+="%s ----------> Slave_SQL_Running:%s\n" %(slave_ip,Slave_SQL_Running)
		Slave_SQL_Running="<b><font color=\"#FF0000\">"+Slave_SQL_Running+"</font></b>"
	if Last_Error=="":
		Last_Error="Null"
	else:
		mail_message+="%s ----------> Last_Error:%s\n" %(slave_ip,Last_Error)

	zone_html+="""
	<tr bgcolor="#dfeae4" valign="middle">
	   <td>%s</td>
	   <td>%s</td>
           <td>%s</td>
	   <td>%s</td>
	   <td>%s</td>	
	   <td>%s</td>
	   <td>%s</td>	
	   <td>%s</td>
	   <td>%s</td>	
	</tr>"""   %(slave_id,slave_name,Master_Host,slave_ip,Slave_IO_Running,Slave_SQL_Running,Last_Error,Seconds_Behind_Master,current_time)

	#send mail
	if mail_message !="":
		msg=email.MIMEText.MIMEText(mail_message)
		msg['To']=config_mail_to
		msg['From']=config_mail_from
		msg['Subject']=slave_name+" "+slave_ip+" mysql replication failed"
		smtp_con=smtplib.SMTP()
		smtp_con.connect(config_mail_smtphost)
		smtp_con.sendmail(config_mail_from,config_mail_to,msg.as_string())	
		smtp_con.close()

zone_html+="</center></body></html>"
##write slave status html
slave_html=open(html_file,"w")
slave_html.write(zone_html)
slave_html.close()


